
drop table jms_dm.dm_customer_province_bill_amount_temp_mth_dt;

CREATE TABLE jms_dm.dm_customer_province_bill_amount_temp_mth_dt (
  `send_financial_center_code` varchar(100) NULL COMMENT "所属财务中心code",
  `first_franchisee_code` varchar(100) NULL COMMENT "所属加盟商编码",
  `pick_network_code` varchar(100) NULL COMMENT "网点编码",
  `date_time` date NULL COMMENT "日期",
  `customer_code` varchar(100) NULL COMMENT "客户Code",
  `pick_network_name` varchar(100) NULL COMMENT "网点名称",
  `first_franchisee_name` varchar(100) NULL COMMENT "所属加盟商name",
  `send_financial_center_name` varchar(100) NULL COMMENT "所属财务中心名称",
  `customer_name` varchar(100) NULL COMMENT "客户名称",
  `receiver_province_id` varchar(100) NULL COMMENT "目的省份ID",
  `receiver_province_name` varchar(100) NULL COMMENT "目的省份名称",
  `total_bill_count` decimal(16, 0) NULL COMMENT "总票数",
  `total_weight_count` decimal(16, 2) NULL COMMENT "总重量（内部计费总量）",
  `total_average_weight` decimal(16, 2) NULL COMMENT "均重（总重量/总票数）",
  `total_freight` decimal(16, 2) NULL COMMENT "收入（总运费）",
  `total_transfer_fee` decimal(16, 2) NULL COMMENT "中转费",
  `total_operation_fee` decimal(16, 2) NULL COMMENT "操作费",
  `total_base_dispatch_fee` decimal(16, 2) NULL COMMENT "基础派费",
  `total_piece_fee` decimal(16, 2) NULL COMMENT "续重派费",
  `total_sales_price` decimal(16, 2) NULL COMMENT "面单费",
  `total_bill_amount` decimal(16, 2) NULL COMMENT "取件提成",
  `total_prime_cost` decimal(16, 2) NULL COMMENT "成本",
  `total_profit` decimal(16, 2) NULL COMMENT "利润",
  `total_average_profit` decimal(16, 2) NULL COMMENT "均利（利润/票数）",
  `total_average_prime` decimal(16, 2) NULL COMMENT "单票成本(成本/总票数)",
  `zero_bill_count` decimal(16, 0) NULL COMMENT "w=0票数",
  `zero_weight` decimal(16, 2) NULL COMMENT "w=0重量",
  `zero_average_weight` decimal(16, 2) NULL COMMENT "w=0均重",
  `zero_freight` decimal(16, 2) NULL COMMENT "w=0收入",
  `zero_prime_cost` decimal(16, 2) NULL COMMENT "w=0成本",
  `zero_profit` decimal(16, 2) NULL COMMENT "w=0利润",
  `zero_average_profit` decimal(16, 2) NULL COMMENT "w=0均利（利润/票数）",
  `one_bill_count` decimal(16, 0) NULL COMMENT "w>0 w<=0.5票数",
  `one_weight` decimal(16, 2) NULL COMMENT "w>0 w<=0.5重量",
  `one_average_weight` decimal(16, 2) NULL COMMENT "w>0 w<=0.5均重",
  `one_freight` decimal(16, 2) NULL COMMENT "w>0 w<=0.5收入",
  `one_prime_cost` decimal(16, 2) NULL COMMENT "w>0 w<=0.5成本",
  `one_profit` decimal(16, 2) NULL COMMENT "w>0 w<=0.5利润",
  `one_average_profit` decimal(16, 2) NULL COMMENT "w>0 w<=0.5均利（利润/票数）",
  `two_bill_count` decimal(16, 0) NULL COMMENT "w>0.5 w<=3.2票数",
  `two_weight` decimal(16, 2) NULL COMMENT "w>0.5 w<=3.2重量",
  `two_average_weight` decimal(16, 2) NULL COMMENT "w>0.5 w<=3.2均重",
  `two_freight` decimal(16, 2) NULL COMMENT "w>0.5 w<=3.2收入",
  `two_prime_cost` decimal(16, 2) NULL COMMENT "w>0.5 w<=3.2成本",
  `two_profit` decimal(16, 2) NULL COMMENT "w>0.5 w<=3.2利润",
  `two_average_profit` decimal(16, 2) NULL COMMENT "w>0.5 w<=3.2均利（利润/票数）",
  `three_bill_count` decimal(16, 0) NULL COMMENT "w>3.2 w<=5.2票数",
  `three_weight` decimal(16, 2) NULL COMMENT "w>3.2 w<=5.2重量",
  `three_average_weight` decimal(16, 2) NULL COMMENT "w>3.2 w<=5.2均重",
  `three_freight` decimal(16, 2) NULL COMMENT "w>3.2 w<=5.2收入",
  `three_prime_cost` decimal(16, 2) NULL COMMENT "w>3.2 w<=5.2成本",
  `three_profit` decimal(16, 2) NULL COMMENT "w>3.2 w<=5.2利润",
  `three_average_profit` decimal(16, 2) NULL COMMENT "w>3.2 w<=5.2均利（利润/票数）",
  `four_bill_count` decimal(16, 0) NULL COMMENT "w>5.2 w<=10.3票数",
  `four_weight` decimal(16, 2) NULL COMMENT "w>5.2 w<=10.3重量",
  `four_average_weight` decimal(16, 2) NULL COMMENT "w>5.2 w<=10.3均重",
  `four_freight` decimal(16, 2) NULL COMMENT "w>5.2 w<=10.3收入",
  `four_prime_cost` decimal(16, 2) NULL COMMENT "w>5.2 w<=10.3成本",
  `four_profit` decimal(16, 2) NULL COMMENT "w>5.2 w<=10.3利润",
  `four_average_profit` decimal(16, 2) NULL COMMENT "w>5.2 w<=10.3均利（利润/票数）",
  `five_bill_count` decimal(16, 0) NULL COMMENT "w>10.3票数",
  `five_weight` decimal(16, 2) NULL COMMENT "w>10.3重量",
  `five_average_weight` decimal(16, 2) NULL COMMENT "w>10.3均重",
  `five_freight` decimal(16, 2) NULL COMMENT "w>10.3收入",
  `five_prime_cost` decimal(16, 2) NULL COMMENT "w>10.3成本",
  `five_profit` decimal(16, 2) NULL COMMENT "w>10.3利润",
  `five_average_profit` decimal(16, 2) NULL COMMENT "w>10.3均利（利润/票数）",
  `six_bill_count` decimal(16, 0) NULL COMMENT "0.5＜W≤1票数",
  `six_weight` decimal(16, 2) NULL COMMENT "0.5＜W≤1重量",
  `six_average_weight` decimal(16, 2) NULL COMMENT "0.5＜W≤1均重",
  `six_freight` decimal(16, 2) NULL COMMENT "0.5＜W≤1收入",
  `six_prime_cost` decimal(16, 2) NULL COMMENT "0.5＜W≤1成本",
  `six_profit` decimal(16, 2) NULL COMMENT "0.5＜W≤1利润",
  `six_average_profit` decimal(16, 2) NULL COMMENT "0.5＜W≤1均利",
  `seven_bill_count` decimal(16, 0) NULL COMMENT "1＜W≤2票数",
  `seven_weight` decimal(16, 2) NULL COMMENT "1＜W≤2重量",
  `seven_average_weight` decimal(16, 2) NULL COMMENT "1＜W≤2均重",
  `seven_freight` decimal(16, 2) NULL COMMENT "1＜W≤2收入",
  `seven_prime_cost` decimal(16, 2) NULL COMMENT "1＜W≤2成本",
  `seven_profit` decimal(16, 2) NULL COMMENT "1＜W≤2利润",
  `seven_average_profit` decimal(16, 2) NULL COMMENT "1＜W≤2均利",
  `eight_bill_count` decimal(16, 0) NULL COMMENT "2＜W≤3.2票数",
  `eight_weight` decimal(16, 2) NULL COMMENT "2＜W≤3.2重量",
  `eight_average_weight` decimal(16, 2) NULL COMMENT "2＜W≤3.2均重",
  `eight_freight` decimal(16, 2) NULL COMMENT "2＜W≤3.2收入",
  `eight_prime_cost` decimal(16, 2) NULL COMMENT "2＜W≤3.2成本",
  `eight_profit` decimal(16, 2) NULL COMMENT "2＜W≤3.2利润",
  `eight_average_profit` decimal(16, 2) NULL COMMENT "2＜W≤3.2均利",
  `nine_bill_count` decimal(16, 0) NULL COMMENT "5.2＜W≤20票数",
  `nine_weight` decimal(16, 2) NULL COMMENT "5.2＜W≤20重量",
  `nine_average_weight` decimal(16, 2) NULL COMMENT "5.2＜W≤20均重",
  `nine_freight` decimal(16, 2) NULL COMMENT "5.2＜W≤20收入",
  `nine_prime_cost` decimal(16, 2) NULL COMMENT "5.2＜W≤20成本",
  `nine_profit` decimal(16, 2) NULL COMMENT "5.2＜W≤20利润",
  `nine_average_profit` decimal(16, 2) NULL COMMENT "5.2＜W≤20均利",
  `ten_bill_count` decimal(16, 0) NULL COMMENT "W>20票数",
  `ten_weight` decimal(16, 2) NULL COMMENT "W>20重量",
  `ten_average_weight` decimal(16, 2) NULL COMMENT "W>20均重",
  `ten_freight` decimal(16, 2) NULL COMMENT "W>20收入",
  `ten_prime_cost` decimal(16, 2) NULL COMMENT "W>20成本",
  `ten_profit` decimal(16, 2) NULL COMMENT "W>20利润",
  `ten_average_profit` decimal(16, 2) NULL COMMENT "W>20均利",
  `m_avg_total_bill_count` decimal(16, 2) NULL COMMENT "月日均件量",
  `if_in_province_bill` int(11) NULL COMMENT "是否省内件",
  `total_with_freight` bigint(20) NULL COMMENT "有运费票数",
  `total_without_freight` bigint(20) NULL COMMENT "无运费票数",
  `cust_level` varchar(20) NULL COMMENT "客户等级",
  `lm_cust_level` varchar(20) NULL COMMENT "上月客户等级"
) ENGINE=OLAP
DUPLICATE KEY(`send_financial_center_code`, `first_franchisee_code`, `pick_network_code`,`date_time`,`customer_code`)
COMMENT "网点客户--目的省份财务表-月表"
PARTITION BY RANGE(`date_time`)
(PARTITION p202301 VALUES [('2023-01-01'), ('2023-02-01')),
PARTITION p202302 VALUES [('2023-02-01'), ('2023-03-01')),
PARTITION p202303 VALUES [('2023-03-01'), ('2023-04-01')),
PARTITION p202304 VALUES [('2023-04-01'), ('2023-05-01')),
PARTITION p202305 VALUES [('2023-05-01'), ('2023-06-01')),
PARTITION p202306 VALUES [('2023-06-01'), ('2023-07-01')),
PARTITION p202307 VALUES [('2023-07-01'), ('2023-08-01')),
PARTITION p202308 VALUES [('2023-08-01'), ('2023-09-01')),
PARTITION p202309 VALUES [('2023-09-01'), ('2023-10-01')),
PARTITION p202310 VALUES [('2023-10-01'), ('2023-11-01')),
PARTITION p202311 VALUES [('2023-11-01'), ('2023-12-01')),
PARTITION p202312 VALUES [('2023-12-01'), ('2024-01-01')))
DISTRIBUTED BY HASH(`pick_network_code`) BUCKETS 10
PROPERTIES (
"replication_num" = "3",
"dynamic_partition.enable" = "true",
"dynamic_partition.time_unit" = "MONTH",
"dynamic_partition.time_zone" = "Asia/Shanghai",
"dynamic_partition.start" = "-7",
"dynamic_partition.end" = "2",
"dynamic_partition.prefix" = "p",
"dynamic_partition.buckets" = "4",
"dynamic_partition.start_day_of_month" = "1",
"in_memory" = "false",
"storage_format" = "V2"
);
